/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package src.services.loan;

/**
 *
 * @author jay
 */

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.faces.model.SelectItem;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import src.HibernateUtil;
import src.domain.loan.*;
import src.services.CRUEDService;
import src.util.Constanta;
import src.util.Utils;

public class LoanModel implements java.io.Serializable {
    
    private CRUEDService service;
    
    public LoanModel() {
        service = new CRUEDService();
    }
    
    public Loan getByAccount(String value, Short status) {
        Map<String, String> where = new HashMap<>();
        where.put("loan.loanNo", value);
        where.put("loan.closureDate", null);
        where.put("loan.approvalUid", "notnull");
        
        if (status!= null) {
            where.put("loan.loanStatus", status.toString());
        }
        return (Loan) service.getObjectSQL("from Loan loan", where);
    }
    
    public Loan getById(Integer id) {
        return (Loan)  service.getById(Loan.class, id);
    }
    
    public List<Loan> findAll(Map<String, String> filters) {
        return (List<Loan>) service.findAll("Loan", filters);
    }
    
     //chan
    //kirim sql lengkap beserta where dan orderby
    public List<Loan> search(String SQLFull) {
        return (List<Loan>) service.findAllSQL(SQLFull, null);
    }
    
    public List<Loan> findAllAdvance(Map<String, Object> filters, String orderby) {
        return (List<Loan>) service.findAllSQLAdvance("from Loan", filters, orderby);
    }
    
    public int countAllAdvance(Map<String, Object> filters, String orderby) {
        return service.countAllSQLAdvance("select count(*) from Loan", filters, orderby);
    }
    
    public List<SelectItem> listAll() {
        return service.listAll("Loan", "loanId","loanNo");
    }
   
    public boolean saveData(Loan data) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
        boolean success = false;
        List<Loancoral> loancorals = null;
        
        try {
            Integer loanId  = data.getLoanId();
            if (loanId!=null) {
                for (int i=0; i<data.getLoancorals().size(); i++) {
                    data.getLoancorals().get(i).setId(new LoancoralId(loanId, i+1));

                    List<LoanCoralAttrib> loanCoralAttribs = data.getLoancorals().get(i).getLoanCoralAtrribs();
                    data.getLoancorals().get(i).setLoanCoralAtrribs(null);
                    for (int j=0; j<loanCoralAttribs.size(); j++) 
                        loanCoralAttribs.get(j).setId2(new LoanCoralAttribId(loanId, i+1, j+1));
                    data.getLoancorals().get(i).setLoanCoralAtrribs(loanCoralAttribs);
                }
            }
            
            if (loanId==null) {
                loancorals = data.getLoancorals();
                data.setLoancorals(null);
            }
            
            session.saveOrUpdate(data);
            
            if (loanId==null) {
                loanId = data.getLoanId();
                for (int i=0; i<loancorals.size(); i++) {
                    loancorals.get(i).setId(new LoancoralId(loanId, i+1));

                    List<LoanCoralAttrib> loanCoralAttribs = loancorals.get(i).getLoanCoralAtrribs();
                    loancorals.get(i).setLoanCoralAtrribs(null);
                    for (int j=0; j<loanCoralAttribs.size(); j++) 
                        loanCoralAttribs.get(j).setId2(new LoanCoralAttribId(loanId, i+1, j+1));
                    loancorals.get(i).setLoanCoralAtrribs(loanCoralAttribs);
                }
                
                data.setLoancorals(loancorals);
            }
            
            if (data.getLoanStatus()==Constanta.LOANSTATUS_RUNNING) {
                session.flush();
                Query q = session.createSQLQuery(" { call loan_modul.loan_actions (?) }");
                q.setInteger(0,data.getLoanId());  
                q.executeUpdate();
            }
            tx.commit();
            success = true;  
            
        } catch (RuntimeException e) {
            
            if (tx != null && tx.isActive()) {
                try {
                    tx.rollback();
                } catch (HibernateException e1) {
                    Logger.getLogger(LoanModel.class.getName()).log(Level.SEVERE, null, e1);
                }
            }
            success = false;  
            Logger.getLogger(LoanModel.class.getName()).log(Level.SEVERE, null, e);
        }
        
        finally {}
        return success;
    }
    
    public boolean deleteData(Loan data) {
        return service.deleteData(data);
    }
    
    public boolean approve(Loan data) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
        boolean success = false;
        
        try {
            if (data.getLoanStatus().equals(Constanta.LOANSTATUS_APPROVED)) {
                List<LoanInstallment> installs = new LoanInstallmentModel().calculate(data);
                data.setLoanInstallments(installs);
            } 
            
            session.saveOrUpdate(data);
            if (data.getLoanStatus()==Constanta.LOANSTATUS_RUNNING) {
                session.flush();
                Query q = session.createSQLQuery(" { call loan_modul.loan_actions (?) }");
                q.setInteger(0,data.getLoanId());  
                q.executeUpdate();
            }
            
            tx.commit();
            success = true;  
            
        } catch (RuntimeException e) {
            
            if (tx != null && tx.isActive()) {
                try {
                    tx.rollback();
                } catch (HibernateException e1) {
                    Logger.getLogger(LoanModel.class.getName()).log(Level.SEVERE, null, e1);
                }
            }
            success = false;  
            Logger.getLogger(LoanModel.class.getName()).log(Level.SEVERE, null, e);
        }
        
        finally {}
        return success;
    }
    
    public String getSPKNumber(Integer branch, Integer produk, Integer defaultNumber) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        org.hibernate.Transaction tx = session.beginTransaction();
        Connection con = session.connection();  // obtain JDBC connection from Session object
        String spkNumber = null;
        CallableStatement callableStatement;
        String spNextNumber = "{call numbering_modul.get_next_number_spk(?,?,?,?)}";

        try {
            callableStatement = con.prepareCall(spNextNumber);
            callableStatement.registerOutParameter(1, java.sql.Types.VARCHAR);
            callableStatement.setInt(2, branch);
            callableStatement.setInt(3, produk);
            callableStatement.setInt(4, defaultNumber);
                
            // execute spNextNumber store procedure
            callableStatement.executeUpdate();
            spkNumber = callableStatement.getString(1);
            
            tx.commit();
        } catch (SQLException ex) {
                
            if (tx != null && tx.isActive()) {
                try { tx.rollback();
                } catch (HibernateException e1) {}
            }
                
            Logger.getLogger(LoanModel.class.getName()).log(Level.SEVERE, null, ex);
        }
                
        return spkNumber;
    }
     
    public boolean isNumberExist(String value, Integer id) {
        BigDecimal count= BigDecimal.ZERO;
        if (value != null && !Constanta.AUTO_NUMBERING.equals(value)) {
            String cond = " where loan_no = "+Utils.fmtSQLString(value);
            if (id != null) cond+=" and loan_id <> "+id;
            
            try { count = (BigDecimal) service.getObjectSQLdirect("select count(*) from loan "+cond);
            } catch (RuntimeException e) {count = BigDecimal.ZERO;}
        }
        return (count.compareTo(BigDecimal.ZERO)==0 ? false : true);
    }
    
    
    public boolean isSPKNumberExist(String value, Integer id) {
        BigDecimal count= BigDecimal.ZERO;
        if (value != null && !Constanta.AUTO_NUMBERING.equals(value)) {
            String cond = " where spk_no = "+Utils.fmtSQLString(value);
            if (id != null) cond+=" and loan_id <> "+id;
            
            try { count = (BigDecimal) service.getObjectSQLdirect("select count(*) from loan "+cond);
            } catch (RuntimeException e) {count = BigDecimal.ZERO;}
        }
        return (count.compareTo(BigDecimal.ZERO)==0 ? false : true);
    }
}


